﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using QLKS.DTO;

namespace QLKS.DAO
{
    public class clsQLKS
    {
        SqlConnection con = new SqlConnection();
        string ketnoi = "data source=(local); initial catalog=QuanLyKhachSan; integrated security=true;";
//-------------------------------------------------------------//
#region DÙNG CHUNG

        public clsQLKS()
        {
            con.ConnectionString = ketnoi;
            Open();
        }

        public void Open()
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
        }

        //lay danh sach bang theo ten
        public DataTable GetData(string tenbang)
        {
            Open();
            //Boolean t = false;
            DataTable dt = new DataTable();

            string sql = "SELECT * FROM " + tenbang + " WHERE Xoa = 0";
            SqlCommand cm = new SqlCommand(sql, con);
            //cm.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cm);
            da.Fill(dt);

            con.Close();
            return dt;
        }

        //lay danh sach bang theo ten
        public DataSet GetData_DataSet(string tenbang)
        {
            Open();
            DataSet ds = new DataSet();

            string sql = "SELECT * FROM " + tenbang +" WHERE Xoa = 0";
            SqlCommand cm = new SqlCommand(sql, con);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            da.Fill(ds,"Tam");

            con.Close();
            return ds;
        }

        //lien ket phieu thue voi phong
        public DataSet CTPhieuThue_Phong()
        {
            Open();
            DataSet ds = new DataSet();
            SqlDataAdapter daCTPT = new SqlDataAdapter("SELECT * FROM chitietphieuthue", con);
            SqlDataAdapter daPhong = new SqlDataAdapter("SELECT * FROM phong", con);

            daCTPT.Fill(ds, "CTPT");
            daPhong.Fill(ds, "Phong");

            DataColumn cotCha = new DataColumn();
            DataColumn cotCon = new DataColumn();

            cotCha = ds.Tables["Phong"].Columns["MaPhong"];
            cotCon = ds.Tables["CTPT"].Columns["MaPhong"];

            ds.Relations.Add("Phong_CTPT", cotCha, cotCon);
            return ds;

            con.Close();
        }

        //cập nhật bang, dung cau lenh
        public Boolean UPDATE_INSERT_DELETE(string sql)
        {
            Open();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception err)
            {
                throw new Exception("ban cap nhat bi loi: " + err);

                con.Close();
                return false;
            }
        }
#endregion

//-------------------------------------------------------------//
#region PHÒNG
        //lay phong con trong
        public DataTable PhongTrong()
        {
            Open();
            DataTable dt = new DataTable();
            Boolean t = true;

            string sql = "SELECT * FROM phong WHERE trangthai = '"+t+"'";
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            con.Close();
            return dt;
        }

        //Dem so phong trong
        public int CountPhong()
        {
            Open();

            Boolean t = true;
            string sql = "SELECT COUNT(*) FROM phong WHERE trangthai = '" + t + "'";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            int d = (int)cmd.ExecuteScalar();

            con.Close();
            return d;
        }

        //lay phong theo ma
        public DataTable GetPhong(Phong_DTO p)
        {
            Open();

            DataTable dt = new DataTable();
            string sql = "SELECT * FROM phong WHERE maphong = " + p.MaPhong;
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            con.Close();
            return dt;
        }

        //Cap nhat lai trang thai phong
        public Boolean UPATE_TrangThaiPhong(Phong_DTO p)
        {
            Open();
            string Sql = "UPDATE phong SET trangthai = '" + p.TrangThai + "' WHERE maphong = " + p.MaPhong;
            return UPDATE_INSERT_DELETE(Sql);
        }
#endregion

//--------------------------------------------------------------//
#region PHIẾU THUÊ
        //lay ma phieu thue
        public int GetSoPhieuThue()
        {
            Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string sql = "SELECT MAX(sophieu) FROM phieuthuephong";
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            int ma = int.Parse(cmd.ExecuteScalar().ToString()) + 1;
            
            con.Close();
            return ma;
        }

        //lay phieu thue cua khach hang
        public DataTable GetPTByKH(PhieuThuePhong_DTO pt)
        {
            Open();

            DataTable dt = new DataTable();
            string sql = "SELECT * FROM PhieuThuePhong WHERE TrangThai = 0 AND Xoa = 0 AND MaKH = " + pt.MaKH;
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(dt);

            return dt;
        }

        //them phieu thue
        public Boolean INSERT_PhieuThue(PhieuThuePhong_DTO pt)
        {
            Open();
            string sql = "INSERT INTO phieuthuephong(SoPhieu,NgayThue,MaKH,MaNV,TrangThai) VALUES("+ pt.SoPhieu +", '"+ pt.NgayThue +"' , "+pt.MaKH+", "+pt.MaNV+", " + pt.TrangThai + ")";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //cap nhat trang thai phieu thue
        public Boolean UPDATE_TTPhieuThue(PhieuThuePhong_DTO pt)
        {
            Open();
            string sql = "UPDATE phieuthuephong SET TrangThai = 1 WHERE sophieu = " + pt.SoPhieu + "";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }


        //cap nhat phieu thue
        public Boolean UPDATE_PhieuThue(PhieuThuePhong_DTO pt)
        {
            Open();
            string sql = "UPDATE phieuthuephong SET MaKH = " + pt.MaKH + ", MaNV = " + pt.MaNV + ", NgayThue = '" + pt.NgayThue + "', TrangThai = "+pt.TrangThai+" WHERE sophieu = " + pt.SoPhieu + "";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //Xoa phiếu thuê
        public Boolean DELETE_PhieuThue(PhieuThuePhong_DTO pt)
        {
            Open();
            string sql = "UPDATE phieuthuephong SET Xoa = 1 WHERE sophieu = " + pt.SoPhieu;

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

#endregion

//-------------------------------------------------------------//
#region CHI TIẾT PHIẾU THUÊ
        //them chi tiet phieu thue
        public Boolean INSERT_CTPhieuThue(ChiTietPhieuThue_DTO ct)
        {
            Open();
            string sql = "INSERT INTO chitietphieuthue(SoPhieu,MaPhong,Gia,SoNguoi) VALUES("+ct.SoPhieu+","+ ct.MaPhong +", "+ct.Gia+","+ct.SoNguoi+")";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //xoa chi tiet phieu thue
        public Boolean DELETE_CTPhieuThue(ChiTietPhieuThue_DTO ctpt)
        {
            Open();

            string sql = "DELETE FROM chitietphieuthue WHERE sophieu = " + ctpt.SoPhieu;
            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //lay ma cac phong trong chi tiet phieu thue
        public DataTable PhongTrongPhieuThue(ChiTietPhieuThue_DTO ctpt)
        {
            Open();

            DataTable dt = new DataTable();
            string sql = "SELECT * FROM chitietphieuthue WHERE sophieu = " + ctpt.SoPhieu;
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(dt);

            return dt;
        }

#endregion

//-------------------------------------------------------------//
#region LOẠI PHÒNG
      
        //lay ma loai phong
        public int GetMaLoaiPhong(Phong_DTO p)
        {
            Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string sql = "SELECT loaiphong FROM phong WHERE maphong = " + p.MaPhong;
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            int ma = int.Parse(cmd.ExecuteScalar().ToString());

            con.Close();
            return ma;
        }

        //Lay so nguoi cua loai phong
        public int GetSoNguoi(LoaiPhong_DTO lp)
        {
            Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string sql = "SELECT songuoi FROM loaiphong WHERE maloai = " + lp.MaLoai;
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            int SoNguoi = int.Parse(cmd.ExecuteScalar().ToString());

            con.Close();
            return SoNguoi;
        }
#endregion

//-------------------------------------------------------------//
#region KHÁCH HÀNG
        //kiem tra ma khach hang
        public Boolean CheckMaKhach(KhachHang_DTO kh)
        {
            Open();

            Boolean kq = false;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string sql = "SELECT COUNT(*) FROM KhachHang WHERE makh = " + kh.MaKH;
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            int d = (int)cmd.ExecuteScalar();
            if (d == 1)
            {
                kq = true;
            }

            cmd = new SqlCommand();
            con.Close();
            return kq;
        }
#endregion

//------------------------------------------------------------//
#region NHÂN VIÊN
        //kiem tra ma nhan vien
        public Boolean CheckNhanVien(NhanVien_DTO nv)
        {
            Open();

            Boolean kq = false;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string sql = "SELECT COUNT(*) FROM nhanvien WHERE MaNV = " + nv.MaNV;
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            int d = (int)cmd.ExecuteScalar();
            if (d == 1)
            {
                kq = true;
            }

            cmd = new SqlCommand();
            con.Close();
            return kq;
        }
#endregion

//------------------------------------------------------------//
#region HÓA ĐƠN

        //Lau ma hoa don moi
        public int GetMaHD()
        {
            Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string sql = "SELECT MAX(MaHD) FROM HoaDonThanhToan";
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            int ma = int.Parse(cmd.ExecuteScalar().ToString()) + 1;

            con.Close();
            return ma;
        }

        //them hoa don
        public Boolean INSERT_HoaDon(HoaDonThanhToan_DTO hd)
        {
            Open();
            string sql = "INSERT INTO HoaDonThanhToan(MaHD,MaKH,MaNV,TriGia,NgayThanhToan) VALUES(" + hd.MaHD + ", " + hd.MaKH + " , " + hd.MaNV + ", " + hd.TriGia + ",'" + hd.NgayThanhToan + "')";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //cap nhat hoa don
        public Boolean UPDATE_HoaDon(HoaDonThanhToan_DTO hd)
        {
            Open();
            string sql = "UPDATE HoaDonThanhToan SET MaKH = " + hd.MaKH + ", MaNV = " + hd.MaNV + " WHERE MaHD = " + hd.MaHD + "";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //Xoa hoa don
        public Boolean DELETE_HoaDon(HoaDonThanhToan_DTO hd)
        {
            Open();
            string sql = "UPDATE HoaDonThanhToan SET Xoa = 1 WHERE MaHD = " + hd.MaHD;

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }
#endregion
//----------------------------------------------------------------//
#region CHI PHI VAT TU
        //Lau ma cpvt moi
        public int GetMaCPVT()
        {
            Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string sql = "SELECT MAX(MaCP) FROM ChiPhiVatTu";
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            int ma = int.Parse(cmd.ExecuteScalar().ToString()) + 1;

            con.Close();
            return ma;
        }

        //them cpvt
        public Boolean INSERT_CPVT(ChiPhiVatTu_DTO cp)
        {
            Open();
            string sql = "INSERT INTO ChiPhiVatTu(MaCP,MaPhong,MaNV,MaVatTu,ChiPhi,NgayLap) VALUES(" + cp.MaCP + ", " + cp.MaPhong + " , " + cp.MaNV + ", " + cp.MaVatTu + "," + cp.ChiPhi + ",'" + cp.NgayLap + "')";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //cap nhat cpvt
        public Boolean UPDATE_CPVT(ChiPhiVatTu_DTO cp)
        {
            Open();
            string sql = "UPDATE ChiPhiVatTu SET MaNV = " + cp.MaNV + ", MaPhong = " + cp.MaPhong + ", MaVatTu = " + cp.MaVatTu + ", ChiPhi = " + cp.ChiPhi + ", NgayLap = '" + cp.NgayLap + "' WHERE MaCP = " + cp.MaCP + "";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //Xoa cpvtn
        public Boolean DELETE_CPVT(ChiPhiVatTu_DTO cp)
        {
            Open();
            string sql = "UPDATE ChiPhiVatTu SET Xoa = 1 WHERE MaCP = " + cp.MaCP;

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }
#endregion
//------------------------------------------------------------//
#region VAT TU
        //Lay ma vat tu moi
        public int GetMaVT()
        {
            Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string sql = "SELECT MAX(MaVatTu) FROM VaTu";
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            int ma = int.Parse(cmd.ExecuteScalar().ToString()) + 1;

            con.Close();
            return ma;
        }

        //them vat tu
        public Boolean INSERT_VT(VatTu_DTO vt)
        {
            Open();
            string sql = "INSERT INTO VatTu(MaVatTu,TenVatTu) VALUES(" + vt.MaVatTu + ", '" + vt.TenVatTu + "')";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //cap nhat vat tu
        public Boolean UPDATE_VT(VatTu_DTO vt)
        {
            Open();
            string sql = "UPDATE VatTu SET TenVatTu = '" + vt.TenVatTu+ "' WHERE MaVatTu = " + vt.MaVatTu + "";

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }

        //Xoa vat tu
        public Boolean DELETE_VT(VatTu_DTO vt)
        {
            Open();
            string sql = "UPDATE VatTu SET Xoa = 1 WHERE MaVatTu = " + vt.MaVatTu;

            con.Close();
            return UPDATE_INSERT_DELETE(sql);
        }
#endregion
    }
}
